package com.gugu.procedure.dao;

import com.gugu.procedure.bean.Production;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface ProductionDao extends JpaRepository <Production, Long>, JpaSpecificationExecutor <Production> {

    Production findTopByOrderNoAndSpecs(String orderNo, String specs);

    @Query(value = "select  ifnull(sum(amount),0) from order_item where order_no=?1 and  specs in(" +
            "select  specs from production where order_no=?1 and procedures like ?2)", nativeQuery = true)
    int queryProductNumByOrderNoAndProduction(String orderNo, String production);


    @Query(value = "select  *  from  production  where order_no=?1  ORDER BY  length(procedures) desc  limit 0,1", nativeQuery = true)
    Production findOrderProcedures(String orderNo);


    @Query(value = "select  procedures  from  production  where order_no=?1  ", nativeQuery = true)
    List <String> findProcedures(String orderNo);

    @Query(value = "select  *  from  production  where order_no=?1  ", nativeQuery = true)
    List <Production> listByOrderNo(String orderNo);

    void deleteAllByOrderNo(String orderNo);

    //查询某个规格工序流转的总数量
    @Query(value = "SELECT ifnull(sum(b.amount),0) " +
            " from  production  a ,order_item b where \n" +
            " a.order_no=b.order_no  and a.specs=b.specs and a.procedures like ?3\n" +
            " and a.order_no=?1 and a.specs=?2", nativeQuery = true)
    int querySpecsProductionAmount(String orderNo, String specs, String production);

    // 查询某个规格工艺已完成的数量
    @Query(value = "select  count(1) from production_record where  order_no=?1 and specs=?2 and status='1' " +
            " and production=?3  and if(?4!='',start_time is not null,1=1) and if(?5!='',end_time is not null,1=1) and if(?6!='',ng_tag=?6,1=1)", nativeQuery = true)
    int querySpecsProductionToBeAmount(String orderNo, String specs, String production, String startTag, String endTag, String ngTag);


    // 查询某个工艺的产品总数和已完成数


    @Query(value = "select  sum(amount) total ,(select  count(1) \n" +
            " from  production_record where order_no=?1 and end_time is not null and status='1' and production=?2) hasDone from order_item where order_no=?1 and specs  in (\n" +
            " select specs from production where  order_no=?1 and procedures like ?3 \n" +
            ")", nativeQuery = true)
    Map <String, Object> query(String orderNo, String production, String likeProduction);


    @Query(value = "select  sum(amount) total ,(select  count(1) " +
            " from  production_record where order_no=?1 and end_time is not null and status='1' and production=?2 and ng_tag='0') hasDone ,(select  count(1)  " +
            "      from  production_record where order_no=?1 and end_time is not null and status='1' and production=?2 and ng_tag='1') ngNum from order_item where order_no=?1 and specs  in ( " +
            " select specs from production where  order_no=?1 and procedures like ?3 " +
            ")", nativeQuery = true)
    Map <String, Object> queryForUpdate(String orderNo, String production, String likeProduction);


    @Query(value = "select  sum(amount) total ,(select  count(1) " +
            " from  production_record where order_no=?1 and end_time is not null and status='1'  and ng_tag='0') hasDone ,(select  count(1)  " +
            "      from  production_record where order_no=?1 and end_time is not null and status='1'  and ng_tag='1') ngNum from order_item where order_no=?1 and specs  in ( " +
            " select specs from production where  order_no=?1 " +
            ")", nativeQuery = true)
    Map <String, Object> queryForUpdate(String orderNo);

}
